﻿CREATE PROCEDURE [dbo].[Forum_ThreadNote_Post]
	@threadId BIGINT,
	@value NVARCHAR(MAX),
	@userId UNIQUEIDENTIFIER
AS
BEGIN
	IF NOT EXISTS(SELECT * FROM [dbo].[forum_Thread] WHERE [ThreadId] = @threadId)
	BEGIN
		RETURN
	END

	DECLARE @isNew BIT = 0

	DECLARE @threadNoteId BIGINT
	SELECT @threadNoteId = [ThreadNoteId] FROM [dbo].[forum_ThreadNote] WHERE [CreatedBy] = @userId AND [ThreadId] = @threadId AND [Text] = @value

	IF @threadNoteId IS NULL
	BEGIN
		BEGIN TRAN
			DECLARE @order INT
			SELECT @order = MAX([Order]) FROM [dbo].[forum_ThreadNote] WHERE [ThreadId] = @threadId

			INSERT INTO [dbo].[forum_ThreadNote]
								 ([Text]
								 ,[ThreadId]
								 ,[Order]
								 ,[CreatedBy])
					 VALUES
								 (@value
								 ,@threadId
								 ,CASE WHEN @order IS NULL THEN 1 ELSE @order + 1 END
								 ,@userId)

			SET @threadNoteId = SCOPE_IDENTITY()

			UPDATE [dbo].[forum_Thread]
				 SET [UpdateBy] = @userId
						,[UpdateDate] = GETUTCDATE()
			WHERE [ThreadId] = @threadId

			SET @isNew = 1			
		COMMIT TRAN
	END

	DECLARE @sectionHashTag NVARCHAR(64)

	SELECT @sectionHashTag =[HashTag]
	FROM [dbo].[forum_Section]
	WHERE [SectionId] = (SELECT TOP 1 [SectionId] FROM [forum_Thread] WHERE [ThreadId] = @threadId)


	SELECT tn.[ThreadNoteId]
				,tn.[Text]
				,tn.[CreatedDate]
				,u.[NickName]
				,u.[EmailSh1]
				,@sectionHashTag AS [SectionHashTag]
				,@threadId AS [ThreadId]
				,@isNew AS [IsNew]
	FROM [dbo].[forum_ThreadNote] AS tn
	INNER JOIN [dbo].[user_User] AS u ON u.[UserId] = tn.[CreatedBy]
	WHERE tn.[ThreadNoteId] = @threadNoteId

END